package com.ph.irp.base.currency.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.currency.dao.PBaseCurrencyDAO;
import com.ph.irp.model.PBaseCurrency;
import com.ph.util.DBUtil;

public class PBaseCurrencyDAOImpl implements PBaseCurrencyDAO {

	private ComboPooledDataSource dataSource;

	public ComboPooledDataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	/*
	 * 查询币别记录实现
	 */
	@Override
	public List<PBaseCurrency> getAllCurrencyList() throws Throwable{
		List<PBaseCurrency> resList = new ArrayList<PBaseCurrency>();
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			StringBuilder sb = new StringBuilder();
			sb.append("select * from p_base_currency");
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while (rs.next()) {
				PBaseCurrency currency = new PBaseCurrency();
				currency.setCurrencyId(rs.getInt("currencyId"));
				currency.setCurrencyCode(rs.getString("currencyCode"));
				currency.setCurrencyName(rs.getString("currencyName"));
				currency.setAccountRate(rs.getFloat("accountRate"));
				currency.setDiscount(rs.getInt("discount"));
				currency.setRates(rs.getInt("rates"));
				currency.setNumDecimal(rs.getInt("numDecimal"));
				resList.add(currency);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return resList;
	}

	/*
	 * 按照币别名称和代码一起查询
	 */
	@Override
	public PBaseCurrency getCurrencyByID(Integer currencyId) throws Throwable{
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		PBaseCurrency currencyModel = new PBaseCurrency();
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("select * from p_base_currency  where currencyId=?");
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.setInt(1, currencyId);
			rs = pStmt.executeQuery();

			rs.next();
			currencyModel.setCurrencyId(currencyId);
			currencyModel.setCurrencyCode(rs.getString("currencyCode"));
			currencyModel.setCurrencyName(rs.getString("currencyName"));
			currencyModel.setAccountRate(rs.getFloat("accountRate"));
			currencyModel.setDiscount(rs.getInt("discount"));
			currencyModel.setRates(rs.getInt("rates"));
			currencyModel.setNumDecimal(rs.getInt("numDecimal"));
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return currencyModel;
	}

	/*
	 * 添加币别记录实现
	 */
	@Override
	public void addCurrency(PBaseCurrency currencyModel) throws Throwable{
		// TODO Auto-generated method stub

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			StringBuilder sBuilder = new StringBuilder();
			sBuilder.append("insert into p_base_currency");
			sBuilder
					.append("(currencyCode,currencyName,AccountRate,discount,rates,numDecimal)");
			sBuilder.append(" values (");
			sBuilder.append("'" + currencyModel.getCurrencyCode() + "'");
			sBuilder.append(",");
			sBuilder.append("'" + currencyModel.getCurrencyName() + "'");
			sBuilder.append(",");
			sBuilder.append(currencyModel.getAccountRate());
			sBuilder.append(",");
			sBuilder.append(currencyModel.getDiscount());
			sBuilder.append(",");
			sBuilder.append(currencyModel.getRates());
			sBuilder.append(",");
			sBuilder.append(currencyModel.getNumDecimal());
			sBuilder.append(")");
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sBuilder.toString());

			System.out.println(currencyModel.getCurrencyCode() + "--"
					+ currencyModel.getCurrencyName() + "--"
					+ currencyModel.getAccountRate() + "--"
					+ currencyModel.getDiscount() + "--"
					+ currencyModel.getRates() + "--"
					+ currencyModel.getNumDecimal());
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}

	/*
	 * 按照币别代码查询
	 */
	@Override
	public List<PBaseCurrency> getCurrencyByCode(String currencyCode) throws Throwable{
		// TODO Auto-generated method stub
		List<PBaseCurrency> resList = new ArrayList<PBaseCurrency>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			StringBuilder sb = new StringBuilder();
			sb.append("select * from p_base_currency ");
			sb.append(" where currencyCode like '%").append(currencyCode)
					.append("%'");
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {

				PBaseCurrency currency = new PBaseCurrency();
				currency.setCurrencyCode(rs.getString("currencyId"));
				currency.setCurrencyCode(rs.getString("currencyCode"));
				currency.setCurrencyName(rs.getString("currencyName"));
				currency.setAccountRate(rs.getFloat("accountRate"));
				currency.setDiscount(rs.getInt("discount"));
				currency.setRates(rs.getInt("rates"));
				currency.setNumDecimal(rs.getInt("numDecimal"));

				resList.add(currency);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	/*
	 * 按照币别名称查询
	 */
	@Override
	public List<PBaseCurrency> getCurrencyByName(String currencyName) throws Throwable{
		// TODO Auto-generated method stub
		List<PBaseCurrency> resList = new ArrayList<PBaseCurrency>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			StringBuilder sb = new StringBuilder();
			sb.append("select * from p_base_currency ");
			sb.append(" where currencyName like '%").append(currencyName)
					.append("%'");
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {

				PBaseCurrency currency = new PBaseCurrency();
				currency.setCurrencyCode(rs.getString("currencyId"));
				currency.setCurrencyCode(rs.getString("currencyCode"));
				currency.setCurrencyName(rs.getString("currencyName"));
				currency.setAccountRate(rs.getFloat("accountRate"));
				currency.setDiscount(rs.getInt("discount"));
				currency.setRates(rs.getInt("rates"));
				currency.setNumDecimal(rs.getInt("numDecimal"));

				resList.add(currency);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	/*
	 * 按照币别名称和代码一起查询
	 */
	@Override
	public List<PBaseCurrency> getCurrencyByCodeAndName(String currencyCode,
			String currencyName) throws Throwable{
		// TODO Auto-generated method stub
		List<PBaseCurrency> resList = new ArrayList<PBaseCurrency>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			StringBuilder sb = new StringBuilder();
			sb.append("select * from p_base_currency");
			sb.append(" where currencyCode like '%").append(currencyCode)
					.append("%'");
			sb.append(" and currencyName like '%").append(currencyName).append(
					"%'");
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {

				PBaseCurrency currency = new PBaseCurrency();
				currency.setCurrencyCode(rs.getString("currencyId"));
				currency.setCurrencyCode(rs.getString("currencyCode"));
				currency.setCurrencyName(rs.getString("currencyName"));
				currency.setAccountRate(rs.getFloat("accountRate"));
				currency.setDiscount(rs.getInt("discount"));
				currency.setRates(rs.getInt("rates"));
				currency.setNumDecimal(rs.getInt("numDecimal"));

				resList.add(currency);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return resList;
	}

	/*
	 * 更新币别记录
	 */
	@Override
	public void updateCurrency(PBaseCurrency currencyModel) throws Throwable{
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			// 修改语句
			StringBuilder sb = new StringBuilder();
			sb.append("update p_base_currency set ");
			sb.append("currencyCode=?,");
			sb.append("currencyName=?,");
			sb.append("AccountRate=?,");
			sb.append("discount=?,");
			sb.append("rates=?,");
			sb.append("numDecimal=? ");
			sb.append("where currencyId=?");

			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());

			pStmt.setString(1, currencyModel.getCurrencyCode());
			pStmt.setString(2, currencyModel.getCurrencyName());
			pStmt.setFloat(3, currencyModel.getAccountRate());
			pStmt.setInt(4, currencyModel.getDiscount());
			pStmt.setInt(5, currencyModel.getRates());
			pStmt.setInt(6, currencyModel.getNumDecimal());
			pStmt.setInt(7, currencyModel.getCurrencyId());

			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	/*
	 * 删除选中记录
	 */
	@Override
	public int delectCurrency(Integer currencyId) throws Throwable{
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		int result = 0;
		try {
			// 删除语句
			StringBuilder sb = new StringBuilder();
			sb.append("delete from p_base_currency ");
			sb.append("where currencyId=?");

			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());

			PBaseCurrency currencyModel = new PBaseCurrency();
			currencyModel.setCurrencyId(currencyId);

			pStmt.setInt(1, currencyModel.getCurrencyId());
			pStmt.executeUpdate();
			result = 1;
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return result;
	}
}
